'''
代码实现了以下功能：
从pmg_attendance_logic.py中调用fetch_and_analysis函数获取指定日期范围的考勤数据
读入驻点人员表格并转换为DataFrame
根据姓名与考勤数据进行左连接对于上班状态或下班状态为空的记录，
检查LeaveRecord中是否有休假记录，如果有则填充为"休假"将处理后的结果写回驻点人员表格
'''
from this import d
import pandas as pd
from openpyxl import load_workbook
import os
from pathlib import Path
from datetime import datetime, timedelta
import sys

project_root = Path(__file__).resolve().parents[2] #向上二级
# 将项目根目录添加到系统路径
sys.path.append(str(project_root))
# 导入自定义模块
from pages.pmg_attendance.pmg_attendance_logic import PMGAttendanceAnalyzer
from entity.leave_record import LeaveRecord
from entity.daily_attendance import DailyAttendance

def load_staff_data(excel_path):
    """
    读入驻点人员表格数据
    
    参数:
        excel_path: Excel文件路径
    
    返回:
        (工作簿对象, 工作表对象, 驻点人员数据DataFrame, 列名列表)
    """
    wb = load_workbook(excel_path)
    ws = wb['驻点人员']
    
    # 将驻点人员表格转换为DataFrame
    data = ws.values
    columns = next(data)  # 获取表头
    staff_df = pd.DataFrame(data, columns=columns)
    
    return wb, ws, staff_df, columns

def generate_date_range(start_date_str:str, end_date_str:str)->list[datetime]:
    """
    生成日期范围
    
    参数:
        start_date_str: 开始日期，格式如'2023-01-01'
        end_date_str: 结束日期，格式如'2023-01-31'
    
    返回:
        日期对象列表
    """
    start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
    end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
    date_range = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]
    return date_range

def add_new_columns_update(ws, date_range:list[datetime], analyzer:PMGAttendanceAnalyzer):
    """
    为每天添加新列,并遍历填充考勤数据
    """
    last_col = ws.max_column
    for date_obj in date_range:
        month_day = date_obj.strftime("%m%d")
        arrival_col = f"{month_day} 上班状态"
        departure_col = f"{month_day} 下班状态"
        
        # 添加第一行列名
        ws.cell(row=1, column=last_col + 1, value=arrival_col)
        ws.cell(row=1, column=last_col + 2, value=departure_col)
        
        # 更新员工考勤数据
        update_staff_attendance(ws, last_col, date_obj, analyzer)
        
        last_col += 2
    
    return []  # 根据实际需求返回新列信息

def update_staff_attendance(ws, last_col, date_obj:list[datetime], analyzer:PMGAttendanceAnalyzer):
    """
    更新驻点人员记录,把考勤数据和休假数据填充到对应的单元格中
    
    参数:
        ws: 工作表对象
        last_col: 工作表最后列，+1为新添加的上班状态列，+2为新加的下班状态列
        date_obj: 日期
        excel_path: Excel文件路径
    """
    leave_record = LeaveRecord()
    # 遍历从第二行到最后一行
    for row_idx in range(2, ws.max_row + 1):
        # 获取当前行的姓名（第一列）
        name = ws.cell(row=row_idx, column=1).value
        if not name:
            continue  # 如果姓名为空，跳过当前行
       
        print(f"姓名: {name}, 日期: {date_obj}")
        # is_on_leave = leave_record.is_on_leave_by_name(name, date_obj)
        # if is_on_leave:
        #     ws.cell(row=row_idx, column=last_col + 2).value = '休假'
        #     ws.cell(row=row_idx, column=last_col + 1).value = '体假'
        #     continue
        # 使用get_attendance_status_by_name获取考勤状态
        attendance_status = analyzer.get_attendance_status_by_name(name, date_obj)
        if attendance_status:
            arrival_status = attendance_status.get('arrival_status')
            arrival_time = attendance_status.get('arrival_time', '')
            departure_status = attendance_status.get('departure_status')
            departure_time = attendance_status.get('departure_time', '')
            # 处理上班状态
            if arrival_status:
                if arrival_status == '正常':
                    ws.cell(row=row_idx, column=last_col + 1).value = '正常'
                else:
                    ws.cell(row=row_idx, column=last_col + 1).value = f"{arrival_status}{arrival_time}"
            else:
                # 检查是否休假
                is_on_leave = leave_record.is_on_leave_by_name(name, date_obj.replace(hour=8, minute=30))
                if is_on_leave:
                    ws.cell(row=row_idx, column=last_col + 1).value = '休假'
            
            # 处理下班状态
            if departure_status:
                if departure_status == '正常':
                    ws.cell(row=row_idx, column=last_col + 2).value = '正常'
                else:
                    ws.cell(row=row_idx, column=last_col + 2).value = f"{departure_status}{departure_time}"
            else:
                # 检查是否休假
                is_on_leave = leave_record.is_on_leave_by_name(name, date_obj.replace(hour=17, minute=30))
                if is_on_leave:
                    ws.cell(row=row_idx, column=last_col + 2).value = '休假'

def process_pmg_report(start_date_str:str, end_date_str:str):
    """
    处理PMG考勤报表
    """
    # 假设excel_path是从某个配置或参数中获取的
    excel_path = os.path.join(project_root, 'img', 'pmg.xlsx')
 # 加载员工数据
    wb, ws, staff_df, current_columns = load_staff_data(excel_path)

    print(f"当前列数: {ws.max_column}")
    
    # 生成日期范围
    date_range = generate_date_range(start_date_str, end_date_str)

    #BI每日考勤数据
    analyzer = PMGAttendanceAnalyzer()
    
    # 为每个日期添加新列
    new_columns = add_new_columns_update(ws, date_range, analyzer)
    
    print(f"当前列数: {ws.max_column}")
    
    # 保存工作簿
    wb.save(excel_path)
    print(f"报表已保存到: {excel_path}")


# 示例用法
if __name__ == "__main__":
    # 示例日期范围
    start_date = "2025-08-04"
    end_date = "2025-08-04"
    process_pmg_report(start_date, end_date)

